using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Office.Interop;
using MigraDoc.DocumentObjectModel;
using MigraDoc.Rendering;

public class <Database>DataClass
{
    public static string connectionString
            = "<CSConnectionString>";

    public static SqlConnection GetConnection()
    {
        SqlConnection connection = new SqlConnection(connectionString);
        return connection;
    }

    public void ExportToExcel(string Type, string Head, System.Windows.Forms.DataGridView Grid)
    {
        try {
            if (Grid.ColumnCount > 0)
            {
                Microsoft.Office.Interop.Excel.Application oExcel;
                Microsoft.Office.Interop.Excel.Workbook oBook;
                Microsoft.Office.Interop.Excel.Worksheet oSheet;

                oExcel = new Microsoft.Office.Interop.Excel.Application();
                oExcel.Visible = false;

                oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(System.Type.Missing));
                oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.ActiveSheet;

                oSheet.Range["B2"].Value = Head;
                oSheet.Range["B2"].Font.Bold = true;

                if (Type == "One")
                {
                    for (int s = 0; s <= Grid.ColumnCount - 1; s++)
                    {
                        oSheet.Range["B" + (4 + s)].Value = Grid.Columns[s].HeaderText;
                        oSheet.Range["C" + (4 + s)].Value = Grid.SelectedRows[0].Cells[s].Value;
                    }

                    oSheet.Columns["B:B"].ColumnWidth = 20;
                    oSheet.Columns.AutoFit();

                }
                else if (Type == "Many")
                {
                    object[,] DataArrayHead = new object[1, Grid.ColumnCount];
                    for (int s = 0; s <= Grid.ColumnCount - 1; s++)
                    {
                        DataArrayHead[0, s] = Grid.Columns[s].HeaderText;
                    }

                    oSheet.Range["B4"].Resize[1, Grid.ColumnCount].Value = DataArrayHead;
                    oSheet.Range["B4"].Resize[1, Grid.ColumnCount].Font.Bold = true;

                    object[,] DataArray = new object[Grid.RowCount, Grid.ColumnCount];
                    for (int r = 0; r <= Grid.RowCount - 1; r++)
                    {
                        for (int s = 0; s <= Grid.ColumnCount - 1; s++)
                        {
                            DataArray[r, s] = Grid[s, r].Value;
                        }
                    }

                    oSheet.Range["B5"].Resize[Grid.RowCount, Grid.ColumnCount].Value = DataArray;
                    oSheet.Columns.AutoFit();

                }

                oExcel.Visible = true;
                oExcel = null;
                oSheet = null;
            }
        }
        catch
        {
        }
    }

    public void ExportToWord(string Type, string Head, System.Windows.Forms.DataGridView Grid)
    {
        try {
            if (Grid.ColumnCount > 0)
            {
                Microsoft.Office.Interop.Word.Application oWord;
                Microsoft.Office.Interop.Word.Document oDoc;
                Microsoft.Office.Interop.Word.Table oTable;
                Microsoft.Office.Interop.Word.Paragraph oPara1;

                oWord = new Microsoft.Office.Interop.Word.Application();
                oWord.Visible = false;
                oDoc = (Microsoft.Office.Interop.Word.Document)(oWord.Documents.Add(System.Type.Missing));
                var _with1 = oDoc.PageSetup;
                _with1.PaperSize = Microsoft.Office.Interop.Word.WdPaperSize.wdPaperA4;
                _with1.Orientation = Microsoft.Office.Interop.Word.WdOrientation.wdOrientLandscape;
                _with1.LeftMargin = oWord.CentimetersToPoints(1);
                _with1.BottomMargin = oWord.CentimetersToPoints(1);
                _with1.TopMargin = oWord.CentimetersToPoints(1);

                oPara1 = (Microsoft.Office.Interop.Word.Paragraph)(oDoc.Content.Paragraphs.Add(System.Type.Missing));
                oPara1.Range.Text = Head;
                oPara1.Range.Font.Bold = 0;
                oPara1.Range.Font.Size = 8;
                oPara1.Format.SpaceAfter = 8;
                oPara1.Range.InsertParagraphAfter();


                if (Type == "One")
                {
                    oTable = oDoc.Tables.Add(oDoc.Bookmarks.get_Item("\\endofdoc").Range, Grid.ColumnCount, 2);
                    oTable.Range.ParagraphFormat.SpaceAfter = 2;

                    for (int i = 0; i <= Grid.ColumnCount - 1; i++)
                    {
                        oTable.Cell(i + 1, 1).Range.InsertAfter(Grid.Columns[i].HeaderText);
                        oTable.Cell(i + 1, 2).Range.Text = Convert.ToString(Grid.SelectedRows[0].Cells[i].Value);
                    }

                    oTable.Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
                    oTable.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleDouble;

                    oTable.AllowAutoFit = true;
                    oTable.Columns.AutoFit();

                }
                else if (Type == "Many")
                {
                    oTable = oDoc.Tables.Add(oDoc.Bookmarks.get_Item("\\endofdoc").Range, Grid.RowCount + 1, Grid.ColumnCount);
                    oTable.Range.ParagraphFormat.SpaceAfter = 2;

                    for (int i = 0; i <= Grid.ColumnCount - 1; i++)
                    {
                        oTable.Cell(1, i + 1).Range.InsertAfter(Grid.Columns[i].HeaderText);
                    }

                    for (int r = 0; r <= Grid.RowCount - 1; r++)
                    {
                        for (int s = 0; s <= Grid.ColumnCount - 1; s++)
                        {
                            oTable.Cell(r + 2, s + 1).Range.Text = Convert.ToString(Grid[s, r].Value);
                        }
                    }

                    oTable.Rows[1].Cells.Shading.BackgroundPatternColorIndex = Microsoft.Office.Interop.Word.WdColorIndex.wdGray25;

                    oTable.Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
                    oTable.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleDouble;

                    oTable.AllowAutoFit = true;
                    oTable.Columns.AutoFit();

                }

                oWord.Visible = true;
                oWord.NormalTemplate.Saved = true;
                oWord = null;
                oDoc = null;
            }
        }
        catch
        {
        }
    }

        //public void ExportToPDF(string Type, string Head, System.Windows.Forms.DataGridView Grid)
        //{
        //    try
        //    {
        //        if (Grid.ColumnCount > 0)
        //        {
        //            Microsoft.Office.Interop.Word.Application oWord;
        //            Microsoft.Office.Interop.Word.Document oDoc;
        //            Microsoft.Office.Interop.Word.Table oTable;
        //            Microsoft.Office.Interop.Word.Paragraph oPara1;
    
        //            oWord = new Microsoft.Office.Interop.Word.Application();
        //            oWord.Visible = false;
        //            oDoc = (Microsoft.Office.Interop.Word.Document)(oWord.Documents.Add(System.Type.Missing));
        //            var _with1 = oDoc.PageSetup;
        //            _with1.PaperSize = Microsoft.Office.Interop.Word.WdPaperSize.wdPaperA4;
        //            _with1.Orientation = Microsoft.Office.Interop.Word.WdOrientation.wdOrientLandscape;
        //            _with1.LeftMargin = oWord.CentimetersToPoints(1);
        //            _with1.BottomMargin = oWord.CentimetersToPoints(1);
        //            _with1.TopMargin = oWord.CentimetersToPoints(1);

        //            oPara1 = (Microsoft.Office.Interop.Word.Paragraph)(oDoc.Content.Paragraphs.Add(System.Type.Missing));
        //            oPara1.Range.Text = Head;
        //            oPara1.Range.Font.Bold = 0;
        //            oPara1.Range.Font.Size = 8;
        //            oPara1.Format.SpaceAfter = 8;
        //            oPara1.Range.InsertParagraphAfter();


        //            if (Type == "One")
        //            {
        //                oTable = oDoc.Tables.Add(oDoc.Bookmarks.get_Item("\\endofdoc").Range, Grid.ColumnCount, 2);
        //                oTable.Range.ParagraphFormat.SpaceAfter = 2;
    
        //                for (int i = 0; i <= Grid.ColumnCount - 1; i++)
        //                {
        //                    oTable.Cell(i + 1, 1).Range.InsertAfter(Grid.Columns[i].HeaderText);
        //                    oTable.Cell(i + 1, 2).Range.Text = Convert.ToString(Grid.SelectedRows[0].Cells[i].Value);
        //                }
    
        //                oTable.Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
        //                oTable.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleDouble;
    
        //                oTable.AllowAutoFit = true;
        //                oTable.Columns.AutoFit();
    
        //            }
        //            else if (Type == "Many")
        //            {
        //                oTable = oDoc.Tables.Add(oDoc.Bookmarks.get_Item("\\endofdoc").Range, Grid.RowCount + 1, Grid.ColumnCount);
        //                oTable.Range.ParagraphFormat.SpaceAfter = 2;
    
        //                for (int i = 0; i <= Grid.ColumnCount - 1; i++)
        //                {
        //                    oTable.Cell(1, i + 1).Range.InsertAfter(Grid.Columns[i].HeaderText);
        //                }

        //                for (int r = 0; r <= Grid.RowCount - 1; r++)
        //                {
        //                    for (int s = 0; s <= Grid.ColumnCount - 1; s++)
        //                    {
        //                        oTable.Cell(r + 2, s + 1).Range.Text = Convert.ToString(Grid[s, r].Value);
        //                    }
        //                }

        //                oTable.Rows[1].Cells.Shading.BackgroundPatternColorIndex = Microsoft.Office.Interop.Word.WdColorIndex.wdGray25;

        //                oTable.Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
        //                oTable.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleDouble;
    
        //                oTable.AllowAutoFit = true;
        //                oTable.Columns.AutoFit();
    
        //            }

        //            string FilePath = System.IO.Path.GetTempPath() + "\\" + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".pdf";
        //            oDoc.ExportAsFixedFormat(FilePath, Microsoft.Office.Interop.Word.WdExportFormat.wdExportFormatPDF, true);
        //            ((Microsoft.Office.Interop.Word._Document)(oDoc)).Close(Microsoft.Office.Interop.Word.WdSaveOptions.wdDoNotSaveChanges);
        //            ((Microsoft.Office.Interop.Word._Application)(oWord)).Quit(); 
        //        }
        //    }
        //    catch
        //    {
        //    }
        //}

        public void ExportToPDF(string Type, string Head, System.Windows.Forms.DataGridView Grid, System.Windows.Forms.DataGridViewRow GridRow)
        {
            try
            {
                DataTable dt = default(DataTable);
                if (Type == "One")
                {
                    dt = DataGridViewRow_To_DataTable(Grid, GridRow, Head);
                }
                else
                {
                    dt = DataGridView_To_DataTable(Grid, Head);
                }
                PDFform pdfForm = new PDFform(dt, Head, Type);

                Document document = pdfForm.CreateDocument();
                document.UseCmykColor = true;
                PdfDocumentRenderer pdfRenderer = new PdfDocumentRenderer(true);
                pdfRenderer.Document = document;
                pdfRenderer.RenderDocument();

                string FilePath = System.IO.Path.GetTempPath() + "\\" + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".pdf";
                pdfRenderer.Save(FilePath);
                System.Diagnostics.Process.Start(FilePath);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadLine();
            }
        }

        //Converts the DataGridView to DataTable
        public DataTable DataGridView_To_DataTable(System.Windows.Forms.DataGridView dgv, String tblName, int minRow = 0)
        {
            DataTable dt = new DataTable(tblName);

            // Header columns
            foreach (System.Windows.Forms.DataGridViewColumn column in dgv.Columns)
            {
                DataColumn dc = new DataColumn(column.Name.ToString());
                dt.Columns.Add(dc);
            }

            // Data cells
            for (int i = 0; i <= dgv.Rows.Count - 1; i++)
            {
                System.Windows.Forms.DataGridViewRow row = dgv.Rows[i];
                DataRow dr = dt.NewRow();
                for (int j = 0; j <= dgv.Columns.Count - 1; j++)
                {
                    dr[j] = (row.Cells[j].Value == null) ? "" : row.Cells[j].Value.ToString();
                }
                dt.Rows.Add(dr);
            }

            // Related to the bug arround min size when using ExcelLibrary for export
            for (int i = dgv.Rows.Count; i <= minRow - 1; i++)
            {
                DataRow dr = dt.NewRow();
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    dr[j] = "  ";
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

        //Converts the DataGridViewRow to DataTable
        public DataTable DataGridViewRow_To_DataTable(System.Windows.Forms.DataGridView dgv, System.Windows.Forms.DataGridViewRow dgvr, String tblName, int minRow = 0)
        {
            DataTable dt = new DataTable(tblName);

            // Header columns
            DataColumn dc1 = new DataColumn("Column");
            dt.Columns.Add(dc1);
            DataColumn dc2 = new DataColumn("Value");
            dt.Columns.Add(dc2);

            foreach (System.Windows.Forms.DataGridViewColumn column in dgv.Columns)
            {
                dt.Rows.Add(dgv.Columns[column.Index].Name.ToString(), dgvr.Cells[column.Index].Value);
            }
            return dt;
        }


}




